﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using Interfaces;
using Entities.Model;
using System.IO;
using System.Data.SqlClient;

namespace Entities.DB
{
    public class UserDBManager : IUserDBManager
    {
        private DataAccessLayer dal;

        public UserDBManager()
        {
            dal = new DataAccessLayer();
            
        }

        public int CreateUser(IUsers _User)
        {
            dal.SetQuery("INSERT INTO [user_login] ([UserID], [UserName], [Login], [Token]) VALUES (@UserID, @UserName, @Login, @Token); INSERT INTO [user_info] ([UserID], [Email], [Address], [Telephone], [Country], [Postcode], [FirstName], [LastName]) VALUES (@UserID, @Email, @Address, @Telephone, @Country, @PostCode, @FirstName, @LastName)");

            dal.AddParameter("@UserID", _User.User_id, System.Data.DbType.String);
            dal.AddParameter("@UserName", _User.User_name, System.Data.DbType.String);
            dal.AddParameter("@Login", _User.Login, System.Data.DbType.String);
            dal.AddParameter("@Token", _User.Token, System.Data.DbType.String);
            dal.AddParameter("@Email", _User.Email, System.Data.DbType.String);
            dal.AddParameter("@Address", _User.Address, System.Data.DbType.String);
            dal.AddParameter("@Telephone", _User.Telephone, System.Data.DbType.String);
            dal.AddParameter("@Country", _User.Country, System.Data.DbType.String);
            dal.AddParameter("@PostCode", _User.PostCode, System.Data.DbType.String);
            dal.AddParameter("@FirstName", _User.FirstName, System.Data.DbType.String);
            dal.AddParameter("@LastName", _User.LastName, System.Data.DbType.String);

            return dal.Execute();
        }

        public int UpdateUser(IUsers _User)
        {
            dal.SetQuery("UPDATE [user_login] SET [UserName] = @UserName, [Login] = @Login, [Token] = @Token WHERE UserID = @UserID; UPDATE [user_info] SET [Email] = @Email, [Address] = @Address, [Telephone] = @Telephone, [Country] = @Country, [PostCode] = @PostCode, [FirstName] = @FirstName, [LastName] = @LastName WHERE UserID = @UserID");

            dal.AddParameter("@UserID", _User.User_id, System.Data.DbType.String);
            dal.AddParameter("@UserName", _User.User_name, System.Data.DbType.String);
            dal.AddParameter("@Login", _User.Login, System.Data.DbType.String);
            dal.AddParameter("@Token", _User.Token, System.Data.DbType.String);
            dal.AddParameter("@Email", _User.Email, System.Data.DbType.String);
            dal.AddParameter("@Address", _User.Address, System.Data.DbType.String);
            dal.AddParameter("@Telephone", _User.Telephone, System.Data.DbType.String);
            dal.AddParameter("@Country", _User.Country, System.Data.DbType.String);
            dal.AddParameter("@PostCode", _User.PostCode, System.Data.DbType.String);
            dal.AddParameter("@FirstName", _User.FirstName, System.Data.DbType.String);
            dal.AddParameter("@LastName", _User.LastName, System.Data.DbType.String);
            return dal.Execute();
        }

        public int DeleteUser(string _User_id)
        {
            dal.SetQuery("DELETE FROM [user_info] WHERE UserID = @UserID; DELETE FROM [user_login] WHERE UserID = @UserID");
            dal.AddParameter("@UserID", _User_id, System.Data.DbType.String);
            return dal.Execute();
        }

        public IUsers GetUserByID(string _User_id)
        {
            IUsers user = null;

            dal.SetQuery("SELECT * FROM [user_login] LEFT JOIN [user_info] ON ([user_login].UserID = [user_info].UserID) WHERE [user_login].UserID = @UserID");
            dal.AddParameter("@UserID", _User_id, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    user = ReadUsers(reader);
                }
            }
            else
            {
                return null;
            }
            return user;
        }

        public IList<IUsers> GetUserByName(string _User_FirstName, string _User_LastName)
        {
            IList<IUsers> result = new List<IUsers>();
            IUsers user = null;

            if (_User_FirstName == null || _User_LastName == null)
            {
                return null;
            }

            if ((_User_FirstName.Equals("") && _User_LastName.Equals("")))
            {
                return null;
            }
            else
            {
                if (_User_FirstName.Equals(""))
                {
                    dal.SetQuery("SELECT * FROM [user_login] LEFT JOIN [user_info] ON ([user_login].UserID = [user_info].UserID) WHERE [user_info].LastName = @LastName");
                }
                else
                {
                    if (_User_LastName.Equals(""))
                    {
                        dal.SetQuery("SELECT * FROM [user_login] LEFT JOIN [user_info] ON ([user_login].UserID = [user_info].UserID) WHERE [user_info].FirstName = @FirstName");
                    }
                    else
                    {
                        dal.SetQuery("SELECT * FROM [user_login] LEFT JOIN [user_info] ON ([user_login].UserID = [user_info].UserID) WHERE [user_info].FirstName = @FirstName AND [user_info].LastName = @LastName");
                    }
                }
            }

            dal.AddParameter("@FirstName", _User_FirstName, System.Data.DbType.String);
            dal.AddParameter("@LastName", _User_LastName, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    user = null;
                    user = ReadUsers(reader);
                    result.Add(user);
                }
            }
            else
            {
                return null;
            }
            return result;
        }

        private IUsers ReadUsers(SqlDataReader reader)
        {
            IUsers user = new Users();

            user.User_id = reader["UserID"].ToString();
            user.User_name = reader["UserName"].ToString();
            user.Login = reader["Login"].ToString();
            user.Token = reader["Token"].ToString();

            user.Email = reader["Email"].ToString();
            user.Address = reader["Address"].ToString();
            user.Telephone = reader["Telephone"].ToString();
            user.Country = reader["Country"].ToString();
            user.PostCode = reader["PostCode"].ToString();

            return user;
        }
    }
}
